package com.kevin.learn.fee;

import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class HouseOwed {
    public static void main(String[] args) {
        System.out.println(getHouseOwed());
    }

    public static String getHouseOwed(){
        boolean isTotal = false;
        String  communityId = "d60fda2caabc4a1caa0efefc8caa4808";
        String  receivedDate = "2022-01-31";
        String  accountingDateStart = "2021-01-01";
        String  accountingDateEnd = "2021-12-31";
        String  house = "9-1102";
        
        StringBuffer houseOwedSql = new StringBuffer();
        List<Map<String, Object>> feeItemList = new ArrayList<>();
        if(isTotal){
            houseOwedSql.append(" select '合计' accountingStartDate,");
            if (feeItemList.size() > 0) {
                for (Map<String, Object> feeItemMap : feeItemList) {
                    houseOwedSql.append("sum(feeItem" + feeItemMap.get("feeItemCode") + ") feeItem" + feeItemMap.get("feeItemCode") + ", ");
                }
            }
            houseOwedSql.append(" sum(fineOwedTotal) fineOwedTotal,");
            houseOwedSql.append(" round(sum(amountTotal),2) amountTotal ");
            houseOwedSql.append(" from(");
        }


        houseOwedSql.append(" SELECT zone.name zoneName ,building.name buildingName ,house.name houseName ,customer.name customerName ,");
        houseOwedSql.append(" date_format(min(able_fee.accountingDate),'%Y年%m月') accountingStartDate, ");
        houseOwedSql.append(" date_format(max(able_fee.accountingDate),'%Y年%m月') accountingEndDate, ");
        if (feeItemList.size() > 0) {
            for (Map<String, Object> feeItemMap : feeItemList) {
                houseOwedSql.append(" sum(case able_fee.feeItemId when '" + feeItemMap.get("feeItemId") + "'then able_fee.amount - ifnull(able_fee.amountPaid , 0) - ifnull(able_fee.adjustAmount , 0) else 0.00 end) feeItem" + feeItemMap.get("feeItemCode") + ", ");
            }
        }
        houseOwedSql.append(" (sum(CASE WHEN ROUND((able_fee.fineReceivable - IFNULL(able_fee.finePaid,0)),2) < 0 THEN 0 ELSE ROUND((able_fee.fineReceivable - IFNULL(able_fee.finePaid,0)),2) END) + sum(able_fee.amount  - ifnull(able_fee.amountPaid, 0) - ifnull(able_fee.adjustAmount, 0))) amountTotal,");
        houseOwedSql.append(" sum(CASE WHEN ROUND((able_fee.fineReceivable - IFNULL(able_fee.finePaid,0)),2) < 0 THEN 0 ELSE ROUND((able_fee.fineReceivable - IFNULL(able_fee.finePaid,0)),2) END) fineOwedTotal ");
        houseOwedSql.append(" FROM ( ");
        houseOwedSql.append(" SELECT feeReceivable.billNo, feeReceivable.amount,ROUND(feeReceivable.fineReceivable,2) fineReceivable,feeReceived.finePaid,feeReceivable.accountingDate,feeReceivable.feeItemId,");
        houseOwedSql.append(" feeReceivable.houseId,feeReceivable.customerId,feeAdjust.adjustAmount,feeReceived.feereceivableId,feeReceived.amountPaid");

        houseOwedSql.append(" FROM Property_FeeReceivable feeReceivable LEFT JOIN(SELECT ableRece.feereceivableId ,SUM( ");
        houseOwedSql.append(" CASE WHEN ableRece.feeType = 1 THEN ableRece.amountPaid ELSE 0 END) AS amountPaid, ");
        houseOwedSql.append(" SUM(CASE WHEN ableRece.feeType = 2 THEN ableRece.amountPaid ELSE 0 END) AS finePaid ");
        houseOwedSql.append(" FROM feereceivable_feereceived ableRece ");
        houseOwedSql.append(" WHERE ableRece.communityId='" + communityId + "' ");
        if(StringUtils.isNotBlank(receivedDate)){
            houseOwedSql.append(" AND ableRece.receivedDate <'"+receivedDate+" 23:59:59' ");
        }
        houseOwedSql.append(" GROUP BY ableRece.feereceivableId ) feeReceived ON feeReceivable.id = feeReceived.feeReceivableId");

        houseOwedSql.append(" LEFT JOIN(SELECT adjustItem.feeReceivableId , SUM(adjustItem.adjustAmount) adjustAmount ");
        houseOwedSql.append(" FROM Property_FeeAdjust adjust LEFT JOIN Property_FeeAdjustItem adjustItem ON adjust.id = adjustItem.feeAdjustId WHERE");
        houseOwedSql.append(" adjust.invalid = 0 ");
        if(StringUtils.isNotBlank(receivedDate)){
            houseOwedSql.append(" AND adjust.adjustDate <'"+receivedDate+" 23:59:59' ");
        }
        houseOwedSql.append(" AND  adjust.communityId='" + communityId + "' ");

        houseOwedSql.append(" GROUP BY adjustItem.feeReceivableId ) feeAdjust ON feeReceivable.id = feeAdjust.feeReceivableId");

        houseOwedSql.append(" where  feeReceivable.communityId = '"+communityId+"' ");
        houseOwedSql.append(" AND feeReceivable.BillType <> 5 ");
        houseOwedSql.append(" AND feeReceivable.invalid = 0 ");
        houseOwedSql.append(" AND feeReceivable. STATUS = 1 ");
        if(StringUtils.isNotBlank(accountingDateStart)){
            houseOwedSql.append("AND date_format(feeReceivable.accountingDate,'%Y-%m') >= '"+ accountingDateStart +"' ");
        }
        if(StringUtils.isNotBlank(accountingDateEnd)){
            houseOwedSql.append("AND date_format(feeReceivable.accountingDate,'%Y-%m') <= '"+ accountingDateEnd +"' ");
        }
        houseOwedSql.append(" ) able_fee ");


        houseOwedSql.append(" LEFT JOIN Property_House house ON able_fee.houseId = house.id LEFT JOIN Property_Building building ON house.buildingId = building.id");
        houseOwedSql.append(" LEFT JOIN Property_Zone zone ON house.zoneId = zone.id LEFT JOIN Property_Customer customer ON able_fee.customerId = customer.id");

        houseOwedSql.append(" WHERE able_fee.fineReceivable + able_fee.amount - ifnull(able_fee.adjustAmount, 0) - ifnull(able_fee.amountPaid,0) - ifnull(able_fee.finePaid,0) != 0");//只需要未收齐的

    
        if(StringUtils.isNotBlank(house)){
            houseOwedSql.append(" AND house.name like '%"+StringUtils.trimToEmpty(house)+"%' ");
        }
   
        houseOwedSql.append(" GROUP BY zone.NAME ,building.NAME ,house.NAME ,customer.NAME ,house.id ");

        if (isTotal) {
            houseOwedSql.append(" ) data0  ");
        } else {

            houseOwedSql.append(" ORDER BY zone.NAME ,building.NAME ,house.NAME ,customer.NAME ,house.id");
        }
        String sql = houseOwedSql.toString();
        return sql;
    }
}
